/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package hvkdbmc.setting;

import hvkdbmc.entities.HVKColumnInfo;
import hvkdbmc.entities.HVKTableInfo;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author admin
 */
public class HVKOracleDiscover {

    public static ArrayList<String> getAllTableName(String pDatabaseName) {
        ArrayList<String> tables = new ArrayList<String>();
        try {

            Connection conn = HVKBaseUtils.getOracleConnection();
            ResultSet rs = HVKBaseUtils.ExecuteResultSet(HVKSetting.oracleGetAllTableNames, conn);
            if (rs != null) {
                while (rs.next()) {
                    tables.add(rs.getString("TABLE_NAME"));
                }
            }

        } catch (ClassNotFoundException ex) {
            Logger.getLogger(HVKOracleDiscover.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(HVKOracleDiscover.class.getName()).log(Level.SEVERE, null, ex);
        } catch (Exception ex) {
            Logger.getLogger(HVKOracleDiscover.class.getName()).log(Level.SEVERE, null, ex);
        }
        return tables;
    }

    public static HVKTableInfo getHVKTableInfo(String tableName) {
        HVKTableInfo tableInfo = new HVKTableInfo(tableName);
        tableInfo.name = tableName.trim();
        try {

            Connection conn = HVKBaseUtils.getOracleConnection();

            ResultSet rs = HVKBaseUtils.ExecuteResultSet(HVKSetting.oracleGetAllColumnInfo + "'" + tableName.trim() + "'", conn);
            if (rs != null) {
                while (rs.next()) {
                    HVKColumnInfo columnInfo = new HVKColumnInfo();
                    columnInfo.id = rs.getLong("COLUMN_ID");
                    columnInfo.name = rs.getString("COLUMN_NAME");
                    columnInfo.type = rs.getString("DATA_TYPE").toLowerCase();
                    columnInfo.typeSize = rs.getLong("DATA_LENGTH");
                    columnInfo.isNullAble = rs.getBoolean("NULLABLE");
                    columnInfo.defaultValue = rs.getObject("DATA_DEFAULT");
                    tableInfo.colums.put(columnInfo.name, columnInfo);
                }
            }

            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (Exception ex) {
            }

            Connection conn2 = HVKBaseUtils.getOracleConnection();

            ResultSet rspk = HVKBaseUtils.ExecuteResultSet(HVKSetting.oracleGetPrimaryKeyForColumnInfo + "'" + tableName.trim() + "'", conn2);
            if (rspk != null) {
                while (rspk.next()) {
                    String columnKey = rspk.getString("COLUMN_NAME");
                    tableInfo.getHVKColumnByName(columnKey).isPrimaryKey = true;
                    tableInfo.getHVKColumnByName(columnKey).seq = tableInfo.name + "_SEQ_" + columnKey;
                }
            }
            try {
                if (conn2 != null) {
                    conn2.close();
                }
            } catch (Exception ex) {
            }

        } catch (ClassNotFoundException ex) {
            Logger.getLogger(HVKOracleDiscover.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(HVKOracleDiscover.class.getName()).log(Level.SEVERE, null, ex);
        } catch (Exception ex) {
            Logger.getLogger(HVKOracleDiscover.class.getName()).log(Level.SEVERE, null, ex);
        }
        return tableInfo;
    }

    public static String GetInsertSQLFromTable(HVKTableInfo tableInfo) {
        String alias = tableInfo.getTableClassName() + HVKSetting.suffixXMLMapAlias;

        String columNames = "";
        String valueNames = "";
        String seqField = "";
        String seqValue = "";
        String seqType = "";
        for (HVKColumnInfo columnInfo : tableInfo.colums.values()) {
            columNames += columnInfo.name + ",";
            valueNames += "#" + columnInfo.getFieldName() + "#,";
            if (columnInfo.seq != null && columnInfo.seq.trim().length() > 0) {
                seqValue=columnInfo.seq + ".NEXTVAL";
                seqField = columnInfo.getFieldName();
                seqType =  HVKBaseUtils.ConvertTypeSqlVSJava(columnInfo.type, HVKSetting.oracle_java_jdbc_type_mapped_file);
            }
        }
                
        String result = "\n<insert id=\"insert" + tableInfo.getTableClassName() + "\" parameterClass=\"" + alias + "\">";
        result+="\n    <selectKey\n" +
                "            keyProperty=\""+seqField+"\"\n" +
                "            resultClass=\""+seqType+"\">        \n" +
                "            SELECT "+seqValue+" FROM DUAL\n" +
                "      </selectKey>";
        result += "\nINSERT INTO " + tableInfo.name + "(";
        
        if (columNames.length() > 0 && columNames.endsWith(",")) {
            columNames = columNames.substring(0, columNames.length() - 1);
        }
        if (valueNames.length() > 0 && valueNames.endsWith(",")) {
            valueNames = valueNames.substring(0, valueNames.length() - 1);
        }

        result += columNames + ")";
        result += "\nVALUES(" + valueNames + ")";
        result += "\n</insert>";
        return result;
    }

    public static String GetUpdateSQLFromTable(HVKTableInfo tableInfo) {
        String alias = tableInfo.getTableClassName() + HVKSetting.suffixXMLMapAlias;
        String result = "";
        String wherePrimayKey = "";
        result += "\n<update id=\"update" + tableInfo.getTableClassName() + "\"  parameterClass=\"" + alias + "\">";
        result += "\nUPDATE " + tableInfo.name;
        result += "\nSET ";
        for (HVKColumnInfo columnInfo : tableInfo.colums.values()) {
            result += "\n" + columnInfo.name + "=#" + columnInfo.getFieldName() + "#,";
            if (columnInfo.isPrimaryKey) {
                wherePrimayKey += columnInfo.name + "=#" + columnInfo.getFieldName() + "#,";
            }
        }
        if (result != null && result.trim().length() > 0 && result.trim().endsWith(",")) {
            result = result.substring(0, result.length() - 1);
        }

        if (wherePrimayKey != null && wherePrimayKey.trim().length() > 0 && wherePrimayKey.trim().endsWith(",")) {
            wherePrimayKey = wherePrimayKey.substring(0, wherePrimayKey.length() - 1);
        }
        result += "\n WHERE " + wherePrimayKey;
        result += "</update>";
        return result;
    }

    public static String GetDeleteSQLFromTable(HVKTableInfo tableInfo) {
//        String alias = tableInfo.getTableClassName() + HVKSetting.suffixXMLMapAlias;
        String alias = "java.lang.Long";
        //seqType =  HVKBaseUtils.ConvertTypeSqlVSJava(columnInfo.type, HVKSetting.oracle_java_jdbc_type_mapped_file);
        String result = "";
        String wherePrimayKey = "";
        result += "\n<delete id=\"delete" + tableInfo.getTableClassName() + "\"  parameterClass=\"" + alias + "\">";
        result += "\nDELETE FROM " + tableInfo.name;
        for (HVKColumnInfo columnInfo : tableInfo.colums.values()) {
            if (columnInfo.isPrimaryKey) {
                wherePrimayKey = wherePrimayKey += columnInfo.name + "=#" + columnInfo.getFieldName() + "# AND ";
            }
        }
        if (wherePrimayKey != null && wherePrimayKey.trim().length() > 0 && wherePrimayKey.trim().endsWith("AND")) {
            wherePrimayKey = wherePrimayKey.substring(0, wherePrimayKey.length() - 4);
        }
        result += "\n WHERE " + wherePrimayKey;
        result += "\n</delete>";
        return result;
    }

    public static String GetSelectAllSQLFromTable(HVKTableInfo tableInfo) {
        String tableTrimName = tableInfo.name.toLowerCase().replace("_", "");
        String resultMapName = tableTrimName + HVKSetting.suffixResultMap;

        String result = "\n<select id=\"selectAll" + tableInfo.getTableClassName() + "\" resultMap=\"" + resultMapName + "\">";
        result += "\nSELECT ";
        String columNames = "";
        for (HVKColumnInfo columnInfo : tableInfo.colums.values()) {
            columNames += columnInfo.name + ",";
        }
        if (columNames.length() > 0 && columNames.endsWith(",")) {
            columNames = columNames.substring(0, columNames.length() - 1);
        }
        result += columNames;
        result += "\nFROM " + tableInfo.name;
        result += "\n</select>";
        return result;
    }

    public static String GetSelectByIDSQLFromTable(HVKTableInfo tableInfo) {
//        String alias = tableInfo.getTableClassName() + HVKSetting.suffixXMLMapAlias;
        String alias = "java.lang.Long";
        String tableTrimName = tableInfo.name.toLowerCase().replace("_", "");
        String resultMapName = tableTrimName + HVKSetting.suffixResultMap;
        String wherePrimayKey = "";

        String result = "\n<select id=\"select" + tableInfo.getTableClassName() + "ByID" + "\" resultMap=\"" + resultMapName + "\"  parameterClass=\"" + alias + "\">";
        result += "\nSELECT ";
        String columNames = "";
        for (HVKColumnInfo columnInfo : tableInfo.colums.values()) {
            columNames += columnInfo.name + ",";
            if (columnInfo.isPrimaryKey) {
                wherePrimayKey = wherePrimayKey += columnInfo.name + "=#" + columnInfo.getFieldName() + "# AND ";
            }
        }
        if (columNames.length() > 0 && columNames.endsWith(",")) {
            columNames = columNames.substring(0, columNames.length() - 1);
        }
        if (wherePrimayKey != null && wherePrimayKey.trim().length() > 0 && wherePrimayKey.trim().endsWith("AND")) {
            wherePrimayKey = wherePrimayKey.substring(0, wherePrimayKey.length() - 4);
        }
        result += columNames;
        result += "\nFROM " + tableInfo.name;
        result += "\n WHERE " + wherePrimayKey;
        result += "\n</select>";

        return result;
    }
}
